using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Net.Fan.SmallPig.Common;
using Net.Fan.SmallPig.Common.DBMeta;
using Net.Fan.SmallPig.DataBase;
using System.Collections.Generic;

//using Microsoft.ApplicationBlocks.Data;

namespace Net.Fan.SmallPig.DataAccess
{
    /// <summary>
    /// Workflow DA
    /// </summary>
    public class WorkflowDA
    {
        /// <summary>
        /// Get all Workflows
        /// </summary>
        /// <returns></returns>
        public static DataTable GetWorkflows()
        {
            string sql = "Select * from Workflows";
            return DA_Base.RunReturnDataset(sql).Tables[0];
        }

        public static DataTable GetInUseWorkflows()
        {
            string sql = "Select * from Workflows where InUse='true'";
            return DA_Base.RunReturnDataset(sql).Tables[0];
        }


        public static long InsertWorkflow(string workflowName, string description, string updateUser)
        {
            string sql = "INSERT INTO Workflows (WorkflowName,Description,UpdateUser,UpdateTime) VALUES (@WorkflowName,@Description,@UpdateUser,GetDate()) ";
            sql += " select @@identity";
            SqlParameter[] paras = new SqlParameter[5];
            paras[0] = new SqlParameter("@WorkflowName", workflowName);
            paras[1] = new SqlParameter("@Description", description);
            paras[2] = new SqlParameter("@UpdateUser", updateUser);

            decimal dd = (decimal)DA_Base.ExecuteScalar(sql, paras);
            return Convert.ToInt64(dd);
        }

        /// <summary>
        /// Get Workflow
        /// </summary>
        /// <param name="workflowID"></param>
        /// <returns></returns>
        public static DataRow GetWorkflow(long workflowID)
        {
            string sql = "select * from Workflows where WorkflowID =@WorkflowID ";

            SqlParameter[] para = new SqlParameter[1];
            para[0] = new SqlParameter("@WorkflowID", workflowID);
            DataSet ds = DA_Base.RunReturnDataset(sql, para);

            return ds.Tables[0].Rows[0];
        }

        public static string GetInitState(long workflowID)
        {
            string sql = "select InitState from Workflows where WorkflowID =@WorkflowID ";

            SqlParameter[] para = new SqlParameter[1];
            para[0] = new SqlParameter("@WorkflowID", workflowID);
            object ds = DA_Base.ExecuteScalar(sql, para);

            if (ds is DBNull)
            {
                return null;
            }
            else
            {
                return ds as string;
            }
        }

        public static string GetEndState(long workflowId)
        {
            string sql = "select EndState from Workflows where WorkflowID =@WorkflowID ";

            SqlParameter[] para = new SqlParameter[1];
            para[0] = new SqlParameter("@WorkflowID", workflowId);
            object ds = DA_Base.ExecuteScalar(sql, para);

            if (ds is DBNull)
            {
                return null;
            }
            else
            {
                return ds as string;
            }
        }

        public static int UpdateInitState(long workflowId, string InitState)
        {
            string sql = "UPDATE workflows SET InitState=@InitState WHERE workflowID=@workflowID";
            SqlParameter[] para = new SqlParameter[4];
            para[0] = new SqlParameter("@workflowID", workflowId);
            para[1] = new SqlParameter("@InitState", InitState);

            return DA_Base.ExecuteNonQuery(sql, para);
        }

        public static int UpdateEndState(long workflowId, string endState)
        {
            string sql = "UPDATE workflows SET EndState=@EndState WHERE workflowID=@workflowID";
            SqlParameter[] para = new SqlParameter[4];
            para[0] = new SqlParameter("@workflowID", workflowId);
            para[1] = new SqlParameter("@EndState", endState);

            return DA_Base.ExecuteNonQuery(sql, para);
        }

        public static string GetAction(int activityID)
        {
            string sql = "select Operate from activities where ActivityID =@ActivityID ";

            SqlParameter[] para = new SqlParameter[1];
            para[0] = new SqlParameter("@ActivityID", activityID);
            object o = DA_Base.ExecuteScalar(sql, para);
            if (o is DBNull)
            {
                return string.Empty;
            }
            else
            {
                return o as string;
            }
        }

        public static int UpdateWorkflow(long workflowId, string workflowName, string desc, string updateUser)
        {
            string sql = "UPDATE workflows SET WorkflowName=@WorkflowName,Description=@Description,UpdateUser=@UpdateUser WHERE workflowID=@workflowID";
            SqlParameter[] para = new SqlParameter[4];
            para[0] = new SqlParameter("@workflowID", workflowId);
            para[1] = new SqlParameter("@Description", desc);
            para[2] = new SqlParameter("@UpdateUser", updateUser);
            para[3] = new SqlParameter("@WorkflowName", workflowName);

            return DA_Base.ExecuteNonQuery(sql, para);
        }

        public static int UpdateActivity(long activityId, string currentState, string Operate, string nextState)
        {
            string sql = "UPDATE Activities SET CurrentState=@CurrentState,Operate=@OperateName,NextState=@NextState WHERE ActivityID=@ActivityID";
            SqlParameter[] para = new SqlParameter[4];
            para[0] = new SqlParameter("@ActivityID", activityId);
            para[1] = new SqlParameter("@CurrentState", currentState);
            para[2] = new SqlParameter("@OperateName", Operate);
            para[3] = new SqlParameter("@NextState", nextState);

            return DA_Base.ExecuteNonQuery(sql, para);
        }

        public static long AddActivity(long workflowId, string CurrentState, string Operate, string NextState)
        {
            string sql = "INSERT INTO Activities (WorkflowId,CurrentState,Operate,NextState) VALUES (@WorkflowId,@CurrentState,@Operate,@NextState) ";
            sql += " select @@identity";
            SqlParameter[] paras = new SqlParameter[5];
            paras[0] = new SqlParameter("@WorkflowId", workflowId);
            paras[1] = new SqlParameter("@CurrentState", CurrentState);
            paras[2] = new SqlParameter("@Operate", Operate);
            paras[3] = new SqlParameter("@NextState", NextState);

            decimal dd = (decimal)DA_Base.ExecuteScalar(sql, paras);
            return Convert.ToInt64(dd);
        }

        ///// <summary>
        ///// get Operates
        ///// </summary>
        ///// <param name="currentStatus"></param>
        ///// <param name="permissionName"></param>
        ///// <returns></returns>
        //public static DataTable GetOperates(string currentStatus, string permissionName)
        //{
        //    string sql = "select * from Workflow where CurrentState=@CurrentStatus and PermissionName=@Permission";
        //    SqlParameter[] paras = new SqlParameter[2];
        //    paras[0] = new SqlParameter("@CurrentStatus", currentStatus);
        //    paras[1] = new SqlParameter("@Permission", permissionName);

        //    return DA_Base.RunReturnDataset(sql, paras).Tables[0];
        //}

        /// <summary>
        /// Get all Operates
        /// </summary>
        /// <param name="currentStatus"></param>
        /// <returns></returns>
        public static string[] GetOperates(string currentState, long workflowId)
        {
            DataTable dt = GetActives(currentState, workflowId);
            string[] Operates = new string[dt.Rows.Count];
            int i = 0;
            foreach (DataRow item in dt.Rows)
            {
                Operates[i] = item[WorkflowMeta.Operate] as string;
                i++;
            }
            return Operates;
        }

        public static DataTable GetActives(string currentState, long workflowId)
        {
            string sql = "select * from Activities where CurrentState=@CurrentStatus and WorkflowId=@WorkflowId";
            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@CurrentStatus", currentState);
            paras[1] = new SqlParameter("@WorkflowId", workflowId);
            var dt = DA_Base.RunReturnDataset(sql, paras).Tables[0];
            return dt;
        }

        public static string[] GetAllCurrentStates(long workflowId)
        {
            string sql = "select CurrentState from Activities where WorkflowID=@WorkflowID";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@WorkflowID", workflowId);

            DataTable dt = DA_Base.RunReturnDataset(sql, paras).Tables[0];
            List<string> states = new List<string>();

            foreach (DataRow item in dt.Rows)
            {
                states.Add(item[WorkflowMeta.CurrentState] as string);
            }

            return states.ToArray();
        }

        public static string[] GetAllNextStates(long workflowId)
        {
            string sql = "select NextState from Activities where WorkflowID=@WorkflowID";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@WorkflowID", workflowId);

            DataTable dt = DA_Base.RunReturnDataset(sql, paras).Tables[0];
            List<string> states = new List<string>();

            foreach (DataRow item in dt.Rows)
            {
                states.Add(item[WorkflowMeta.NextState] as string);
            }

            return states.ToArray();
        }

        /// <summary>
        /// get next status
        /// </summary>
        /// <param name="workflowID"></param>
        /// <returns></returns>
        public static string GetNextState(long activityId)
        {
            string sql = "select NextState from Activities where activityId=@activityId";

            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@activityId", activityId);

            return DA_Base.ExecuteScalar(sql, paras) as string;
        }


        public static DataTable GetActivities(long workflowId)
        {
            string sql = "select * from Activities where workflowID=@workflowID";
            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@workflowID", workflowId);

            return DA_Base.RunReturnDataset(sql, paras).Tables[0];
        }

        public static long? GetWorkflowIDbyActivity(long activityID)
        {
            string sql = "select workflowID from Activities where ActivityID=@ActivityID";
            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@ActivityID", activityID);

            object o = DA_Base.ExecuteScalar(sql, paras);

            if (o is DBNull)
            {
                return null;
            }
            else
            {
                return (long)o;
            }
        }

        public static DataRow GetActivity(long activityId)
        {
            string sql = "select * from Activities where ActivityID=@ActivityID";
            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@ActivityID", activityId);
            DataSet ds = DA_Base.RunReturnDataset(sql, paras);
            if (ds.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            else
            {
                return ds.Tables[0].Rows[0];
            }

        }

        public static int DelActivity(long activityId)
        {
            string sql = "delete from Activities where ActivityID=@ActivityID";
            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@ActivityID", activityId);
            int ds = DA_Base.ExecuteNonQuery(sql, paras);
            return ds;
        }



        public static bool GetInUse(long workflowId)
        {
            string sql = "select InUse from Workflows where WorkflowID =@WorkflowID ";

            SqlParameter[] para = new SqlParameter[1];
            para[0] = new SqlParameter("@WorkflowID", workflowId);
            object o = DA_Base.ExecuteScalar(sql, para);

            if (o is DBNull)
            {
                return false;
            }
            else
            {
                return (bool)o;
            }
        }

        public static int UpdateInUse(long workflowId, bool inUse)
        {
            string sql = "UPDATE workflows SET InUse=@InUse WHERE workflowID=@workflowID";
            SqlParameter[] para = new SqlParameter[4];
            para[0] = new SqlParameter("@workflowID", workflowId);
            para[1] = new SqlParameter("@InUse", inUse);

            return DA_Base.ExecuteNonQuery(sql, para);
        }

        public static string GetWorkflowName(long workflowId)
        {
            string sql = "select WorkflowName from Workflows where WorkflowID =@WorkflowID ";

            SqlParameter[] para = new SqlParameter[1];
            para[0] = new SqlParameter("@WorkflowID", workflowId);
            string o = DA_Base.ExecuteScalar(sql, para) as string;
            return o;

        }

        public static List<string> GetOperators(long activityID)
        {
            List<string> ops = new List<string>();

            string sql = "select Operator from ActivityOperators where ActivityID =@ActivityID ";

            SqlParameter[] para = new SqlParameter[1];
            para[0] = new SqlParameter("@ActivityID", activityID);
            DataTable o = DA_Base.RunReturnDataset(sql, para).Tables[0];
            foreach (DataRow item in o.Rows)
            {
                ops.Add(item[0] as string);
            }

            return ops;
        }

        public static string GetOperate(long activityID)
        {

            string sql = "select Operate from Activities where ActivityID =@ActivityID ";

            SqlParameter[] para = new SqlParameter[1];
            para[0] = new SqlParameter("@ActivityID", activityID);
            object o = DA_Base.ExecuteScalar(sql, para);

            if (o is DBNull)
            {
                return null;
            }
            else
            {
                return o as string;
            }
        }

        public static void RemoveOperator(long activityID, string Operator)
        {
            string sql = "Delete from ActivityOperators where ActivityID =@ActivityID and Operator=@Operator";

            SqlParameter[] para = new SqlParameter[2];
            para[0] = new SqlParameter("@ActivityID", activityID);
            para[1] = new SqlParameter("@Operator", Operator);
            int o = DA_Base.ExecuteNonQuery(sql, para);
        }

        public static void InsertOperator(long activityID, string Operator)
        {
            string sql = "Insert into ActivityOperators (ActivityID, Operator) values (@ActivityID ,@Operator)";

            SqlParameter[] para = new SqlParameter[2];
            para[0] = new SqlParameter("@ActivityID", activityID);
            para[1] = new SqlParameter("@Operator", Operator);
            int o = DA_Base.ExecuteNonQuery(sql, para);
        }

        public static void DelOperator(long activeId)
        {
            string sql = "Delete from ActivityOperators where ActivityID =@ActivityID";

            SqlParameter[] para = new SqlParameter[2];
            para[0] = new SqlParameter("@ActivityID", activeId);

            int o = DA_Base.ExecuteNonQuery(sql, para);
        }

        public static DataTable GetActivityOperators(long activityId)
        {
            throw new NotImplementedException();
        }

        public static bool IsActivityOperator(long activityId, string user)
        {
            string sql = "select count(o.ActivityId) from ActivityOperators o where o.ActivityId=@ActivityId and o.Operator=@Operator";

            SqlParameter[] para = new SqlParameter[2];
            para[0] = new SqlParameter("@Operator", user);
            para[1] = new SqlParameter("@ActivityId", activityId);

            int o = (int)DA_Base.ExecuteScalar(sql, para);
            if (o == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public static DataTable GetWhosOperates(long workflowId, string state, string Operator)
        {
            //List<string> ops = new List<string>();

            string sql = "select a.* from ActivityOperators o, Activities a  where a.workflowId=@WorkflowID and a.CurrentState=@CurrentState and a.ActivityID=o.ActivityID and  o.Operator=@Operator";

            SqlParameter[] para = new SqlParameter[3];
            para[0] = new SqlParameter("@WorkflowID", workflowId);
            para[1] = new SqlParameter("@CurrentState", state);
            para[2] = new SqlParameter("@Operator", Operator);

            DataTable o = DA_Base.RunReturnDataset(sql, para).Tables[0];
            return o;
            //foreach (DataRow item in o.Rows)
            //{
            //    ops.Add(item[0] as string);
            //}

            //return ops;
        }




    }
}
